/*
 * This file implements the data model service.
 * @author Group 10
 */

package datamodel;

import java.io.IOException;
import java.rmi.*;
import java.rmi.registry.*;
import java.net.*;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Calendar;

import java.util.HashMap;
import java.util.Map;
import java.util.logging.FileHandler;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.util.logging.SimpleFormatter;

import rmiinterfaces.*;
import rmiinterfaces.RequestTypes.RequestType;


// This class implements an RMI server that encapsulates business logic of the application. 
// It receives abstract requests from GUI forms, runs appropriate queries on the database, 
// and returns the results back to forms. 
public class DataModel extends java.rmi.server.UnicastRemoteObject
        implements DataModelInterface {

    int thisPort;
    String thisAddress;
    Registry registry;    // rmi registry for lookup the remote objects.
    static Integer updateOrderID;
    String SQLServerIP;
    final static Logger logger = Logger.getLogger("Data Model Logger");
    
    // Constructor for DataModel
    public DataModel(String address) throws RemoteException {
        
        // Initialize the logger properly
        try {
            SimpleFormatter sf = new SimpleFormatter();
            SQLServerIP = address;
            FileHandler fh = new FileHandler("data_model_log.txt", true);
            fh.setFormatter(sf);
            logger.addHandler(fh);
        } catch (IOException e) {
            System.out.println("Unable to start logging; proceeding without logs.");
        }
        logger.info("Data Model process started");

        // set up RMI server
        try {
            // get the address of this host.
            thisAddress = (InetAddress.getLocalHost()).toString();
        } catch (Exception e) {
            throw new RemoteException("can't get inet address.");
        }
        thisPort = 1488; 

        try {
            // create the registry and bind the name and object.
            registry = LocateRegistry.createRegistry(thisPort);
            System.out.println(registry.toString());
            registry.rebind("dataModel", this);
        } catch (RemoteException e) {
            throw e;
        }
        
        System.out.println("RMI server dataModel started at " 
                + thisAddress + ", port=" + thisPort);
    }
    
    // main function for DataModel
    static public void main(String args[]) {
        try {
            DataModel dm = new DataModel(args[0]);
            
        } catch (Exception e) {
            e.printStackTrace();
            System.exit(1);
        }
    }

    // This method is called from the remote client by the RMI.
    // Parameters that are passed depend upon the request numbers (see DataModelInterface for description).
    public Map<String, Object> execRequest(RequestType reqNum, Map<String, Object> params) throws RemoteException {
        Map<String, Object> m = null;
        switch (reqNum) {
            case GETTREES:
                m = orderapp_getTrees(params);
                break;
            case GETSHRUBS:
                m = orderapp_getShrubs(params);
                break;
            case GETSEEDS:
                m = orderapp_getSeeds(params);
                break;
            case SUBMITORDER:
                m = orderapp_submitOrder(params);
                // log order submission
                logger.log(Level.INFO, "Order number {0} was created",
                        (String) m.get("orderNumber") + " by user " + (String) params.get("userName"));
                break;
            case GETPENDING:
                m = getPendingOrders(params);
                break;
            case GETSHIPPED:
                m = getShippedOrders(params);
                break;
            case MARKSHIPPED:
                m = markAsShipped(params);
                // log order shipment
                logger.log(Level.INFO, "Order number {0} was shipped",
                        (String) m.get("orderNumber"));
                break;
            case GETINDIVIDUALORDER:
                m = getIndividualOrder(params);
                break;
            case ADDINVENTORY:
                m = addInventory(params);
                break;
            case LISTINVENTORY:
                m = listInventory(params);
                break;

        }

        return m;
    }

    // Implementation of GETTREES request
    private Map<String, Object> orderapp_getTrees(Map<String, Object> m) {

        Boolean connectError = false;       // Error flag
        Connection DBConn = null;           // MySQL connection handle
        String errString = null;            // String for displaying errors
        String msgString = null;            // String for displaying non-error messages
        ResultSet res = null;               // SQL query result set pointer
        Statement s = null;                 // SQL statement pointer
        HashMap<String, Object> param = (HashMap<String, Object>) m;
        HashMap<String, Object> result = new HashMap<String, Object>();

        String messages = "";

        String rows = "";

        try {
            msgString = ">> Establishing Driver...";
            messages = ("\n" + msgString);

            //load JDBC driver class for MySQL
            Class.forName("com.mysql.jdbc.Driver");

            msgString = ">> Setting up URL...";
            messages = ("\n" + msgString);

            //define the data source
            //         String SQLServerIP = (String)param.get("SQLServerIP");
            String sourceURL = "jdbc:mysql://" + SQLServerIP + ":3306/inventory";

            msgString = ">> Establishing connection with: " + sourceURL + "...";
            messages = ("\n" + msgString);

            //create a connection to the db - note the default account is "remote"
            //and the password is "remote_pass" - you will have to set this
            //account up in your database

            DBConn = DriverManager.getConnection(sourceURL, "remote", "remote_pass");

        } catch (Exception e) {

            errString = "\nProblem connecting to database:: " + e;
            result.put("errString", errString);
            connectError = true;

        } // end try-catch

        // If we are connected, then we get the list of seeds from the
        // inventory database

        if (!connectError) {
            try {
                s = DBConn.createStatement();
                res = s.executeQuery("Select * from trees");

                //Display the data in the textarea

                while (res.next()) {
                    msgString = res.getString(1) + " : " + res.getString(2)
                            + " : $" + res.getString(4) + " : " + res.getString(3)
                            + " units in stock";
                    rows = rows + (msgString + "\n");

                } // while

            } catch (Exception e) {

                errString = "\nProblem getting seed inventory:: " + e;
                result.put("errString", errString);

            } // end try-catch
        } // if connect check

        result.put("rows", rows);
        result.put("messages", messages);

        return result;


    }

    // Implementation for GETINDIVIDUALORDER request
    private Map<String, Object> getIndividualOrder(Map<String, Object> m) {
        // This button gets the selected line of text from the
        // order list window jTextArea1. The line of text is parsed for the
        // order number. Once the order number is parsed, then the order is
        // retrieved from the orders database. The ordertabel field from the
        // record contains the name of the table that has the items that make
        // up the order. This table is opened and all the items are listed
        // in jTextArea3.

        Boolean connectError = false;       // Error flag
        Connection DBConn = null;           // MySQL connection handle    
        String errString = null;            // String for displaying errors
        int beginIndex;                     // Parsing index
        int endIndex;                       // Parsing index
        String msgString = null;            // String for displaying non-error messages
        String orderSelection = null;       // Order selected from TextArea1
        String orderTable = null;           // The name of the table containing the order items
        String orderID = null;              // Product ID pnemonic
        String productDescription = null;   // Product description
        ResultSet res = null;               // SQL query result set pointer
        Statement s = null;                 // SQL statement pointer
        Boolean orderBlank = false;         // False: order string is not blank
        String SQLStatement;                // SQL query
        String rows = "";

        String messages = "";

        HashMap<String, Object> param = (HashMap<String, Object>) m;
        HashMap<String, Object> result = new HashMap<String, Object>();

        // this is the selected line of text
        orderSelection = (String) param.get("orderSelection");

        // make sure its not blank
        if (orderSelection.length() > 0) {
            // get the product ID
            beginIndex = 0;
            beginIndex = orderSelection.indexOf(" # ", beginIndex);
            beginIndex = beginIndex + 3; //skip past _#_
            endIndex = orderSelection.indexOf(" :", beginIndex);
            orderID = orderSelection.substring(beginIndex, endIndex);
            
            result.put("orderNumber", orderID);

        } else {

            msgString = ">> Order string is blank...";
            messages = ("\n" + msgString);
            orderBlank = true;

        } // Blank string check

        // If an order was selected, then connect to the orderinfo database. In
        // all normal situations this would be impossible to do since the select
        // button is disabled until an order is selected... just in case the
        // check is here.

        if (!orderBlank) {
            try {
                msgString = ">> Establishing Driver...";
                messages = ("\n" + msgString);

                //load JDBC driver class for MySQL
                Class.forName("com.mysql.jdbc.Driver");

                msgString = ">> Setting up URL...";
                messages = messages + ("\n" + msgString);

                //define the data source
                // String SQLServerIP = (String)param.get("SQLServerIP");
                String sourceURL = "jdbc:mysql://" + SQLServerIP + ":3306/orderinfo";

                msgString = ">> Establishing connection with: " + sourceURL + "...";
                messages = messages + ("\n" + msgString);

                //create a connection to the db - note the default account is "remote"
                //and the password is "remote_pass" - you will have to set this
                //account up in your database
                DBConn = DriverManager.getConnection(sourceURL, "remote", "remote_pass");

            } catch (Exception e) {

                errString = "\nProblem connecting to orderinfo database:: " + e;
                result.put("errString", e.toString());
                connectError = true;

            } // end try-catch

        } // blank order check 

        if (!connectError && !orderBlank) {
            try {
                s = DBConn.createStatement();
                SQLStatement = "SELECT * FROM orders WHERE order_id = " + Integer.parseInt(orderID);
                res = s.executeQuery(SQLStatement);

                // Get the information from the database. Display the
                // first and last name, address, phone number, address, and
                // order date. Same the ordertable name - this is the name of
                // the table that is created when an order is submitted that
                // contains the list of order items.

                while (res.next()) {

                    orderTable = res.getString(9);         // name of table with list of items
                    result.put("first_name", res.getString(3)); // first name
                    result.put("last_name", res.getString(4)); // last name
                    result.put("phone", res.getString(6)); // phone
                    result.put("order_date", res.getString(2)); // order date
                    result.put("address", res.getString(5));  // address

                } // for each element in the return SQL query

                // get the order items from the related order table
                SQLStatement = "SELECT * FROM " + orderTable;
                res = s.executeQuery(SQLStatement);


                // list the items on the form that comprise the order
                // jTextArea3.setText("");

                while (res.next()) {
                    msgString = res.getString(1) + ":  PRODUCT ID: " + res.getString(2)
                            + "  DESCRIPTION: " + res.getString(3) + "  PRICE $" + res.getString(4);
                    rows = rows + (msgString + "\n");

                } // while

                // This global variable is used to update the record as shipped
                updateOrderID = Integer.parseInt(orderID);
                result.put("rows", rows);

                // Update the form
                //    jButton1.setEnabled(true);
                msgString = "RECORD RETRIEVED...";
                result.put("messages", messages);
                result.put("msgString", msgString);


            } catch (Exception e) {

                errString = "\nProblem getting order items:: " + e;
                result.put("errString", e.toString());

            } // end try-catch

        } // connect and blank order check
        return result;

    }

    // Implementation for GETSHRUBS request
    private Map<String, Object> orderapp_getShrubs(Map<String, Object> m) {

        Boolean connectError = false;       // Error flag
        Connection DBConn = null;           // MySQL connection handle
        String errString = null;            // String for displaying errors
        String msgString = null;            // String for displaying non-error messages
        ResultSet res = null;               // SQL query result set pointer
        Statement s = null;                 // SQL statement pointer
        HashMap<String, Object> param = (HashMap<String, Object>) m;
        HashMap<String, Object> result = new HashMap<String, Object>();
        //  ArrayList<String> rows = new ArrayList<String>();
        String rows = "";
        String messages = "";

        try {
            msgString = ">> Establishing Driver...";
            messages = ("\n" + msgString);

            //load JDBC driver class for MySQL
            Class.forName("com.mysql.jdbc.Driver");

            msgString = ">> Setting up URL...";
            messages = ("\n" + msgString);

            //define the data source
            //      String SQLServerIP = (String)param.get("SQLServerIP");
            String sourceURL = "jdbc:mysql://" + SQLServerIP + ":3306/inventory";

            msgString = ">> Establishing connection with: " + sourceURL + "...";
            messages = ("\n" + msgString);

            //create a connection to the db - note the default account is "remote"
            //and the password is "remote_pass" - you will have to set this
            //account up in your database

            DBConn = DriverManager.getConnection(sourceURL, "remote", "remote_pass");

        } catch (Exception e) {

            errString = "\nProblem connecting to database:: " + e;
            result.put("errString", errString);
            connectError = true;

        } // end try-catch

        // If we are connected, then we get the list of seeds from the
        // inventory database

        if (!connectError) {
            try {
                s = DBConn.createStatement();
                res = s.executeQuery("Select * from shrubs");

                //Display the data in the textarea

                //   jTextArea1.setText("");

                while (res.next()) {
                    msgString = res.getString(1) + " : " + res.getString(2)
                            + " : $" + res.getString(4) + " : " + res.getString(3)
                            + " units in stock";
                    rows = rows + (msgString + "\n");

                } // while

            } catch (Exception e) {

                errString = "\nProblem getting seed inventory:: " + e;
                result.put("errString", errString);

            } // end try-catch
        } // if connect check

        result.put("rows", rows);
        result.put("messages", messages);

        return result;

    }

    // Implementation for GETSEEDS request
    private Map<String, Object> orderapp_getSeeds(Map<String, Object> m) {

        Boolean connectError = false;       // Error flag
        Connection DBConn = null;           // MySQL connection handle
        String errString = null;            // String for displaying errors
        String msgString = null;            // String for displaying non-error messages
        ResultSet res = null;               // SQL query result set pointer
        Statement s = null;                 // SQL statement pointer
        HashMap<String, Object> param = (HashMap<String, Object>) m;
        HashMap<String, Object> result = new HashMap<String, Object>();
        //  ArrayList<String> rows = new ArrayList<String>();
        String rows = "";
        String messages = "";

        try {
            msgString = ">> Establishing Driver...";
            messages = ("\n" + msgString);

            //load JDBC driver class for MySQL
            Class.forName("com.mysql.jdbc.Driver");

            msgString = ">> Setting up URL...";
            messages = ("\n" + msgString);

            //define the data source
            //   String SQLServerIP = (String)param.get("SQLServerIP");
            String sourceURL = "jdbc:mysql://" + SQLServerIP + ":3306/inventory";

            msgString = ">> Establishing connection with: " + sourceURL + "...";
            messages = ("\n" + msgString);

            //create a connection to the db - note the default account is "remote"
            //and the password is "remote_pass" - you will have to set this
            //account up in your database

            DBConn = DriverManager.getConnection(sourceURL, "remote", "remote_pass");

        } catch (Exception e) {

            errString = "\nProblem connecting to database:: " + e;
            result.put("errString", errString);
            connectError = true;

        } // end try-catch

        // If we are connected, then we get the list of seeds from the
        // inventory database

        if (!connectError) {
            try {
                s = DBConn.createStatement();
                res = s.executeQuery("Select * from seeds");

                //Display the data in the textarea

                //   jTextArea1.setText("");

                while (res.next()) {
                    msgString = res.getString(1) + " : " + res.getString(2)
                            + " : $" + res.getString(4) + " : " + res.getString(3)
                            + " units in stock";
                    rows = rows + (msgString + "\n");

                } // while

            } catch (Exception e) {

                errString = "\nProblem getting seed inventory:: " + e;
                result.put("errString", errString);

            } // end try-catch
        } // if connect check

        result.put("rows", rows);
        result.put("messages", messages);

        return result;

    }

    // Implementation for SUBMITORDER requrest
    private Map<String, Object> orderapp_submitOrder(Map<String, Object> m) {
        HashMap<String, Object> param = (HashMap<String, Object>) m;
        HashMap<String, Object> result = new HashMap<String, Object>();
        // This is the submit order button. This handler will check to make sure
        // that the customer information is provided, then create an entry in
        // the orderinfo::orders table. It will also create another table where
        // the list of items is stored. This table is also in the orderinfo
        // database as well.

        int beginIndex;                 // String parsing index
        Boolean connectError = false;   // Error flag
        String customerAddress;         // Buyers mailing address
        int endIndex;                   // String paring index
        String firstName = null;        // Customer's first name
        Connection DBConn = null;       // MySQL connection handle
        float fCost;                    // Total order cost
        String description;             // Tree, seed, or shrub description
        Boolean executeError = false;   // Error flag
        String errString = null;        // String for displaying errors
        int executeUpdateVal;           // Return value from execute indicating effected rows
        String lastName = null;         // Customer's last name
        String msgString = null;        // String for displaying non-error messages
        String orderTableName = null;   // This is the name of the table that lists the items
        String sTotalCost = null;       // String representing total order cost
        String sPerUnitCost = null;     // String representation of per unit cost
        String orderItem = null;        // Order line item from jTextArea2
        String phoneNumber = null;      // Customer phone number
        Float perUnitCost;              // Cost per tree, seed, or shrub unit
        String productID = null;        // Product id of tree, seed, or shrub
        Statement s = null;             // SQL statement pointer
        String SQLstatement = null;     // String for building SQL queries
        String messages = "";

        // Check to make sure there is a first name, last name, address and phone

        //jTextField3 changes to firstName
        //jTextField4 changes to lastName
        //jTextField5 changes to phoneNumber
        //jTextArea4 changes to customerAddress
        //jTextArea3 changes to rows
        //jTextField6 changes to sTotalCost
        //jTextArea2 changes to orderString


        if ((((String) param.get("firstName")).length() > 0) && ((String) param.get("lastName")).length() > 0
                && ((String) param.get("phoneNumber")).length() > 0
                && ((String) param.get("customerAddress")).length() > 0) {
            try {
                msgString = ">> Establishing Driver...";
                messages = messages + msgString + "\n";

                //load JDBC driver class for MySQL
                Class.forName("com.mysql.jdbc.Driver");

                msgString = ">> Setting up URL...";
                messages = messages + msgString + "\n";

                //define the data source
                //   String SQLServerIP = (String)param.get("SQLServerIP");
                String sourceURL = "jdbc:mysql://" + SQLServerIP + ":3306/orderinfo";

                msgString = ">> Establishing connection with: " + sourceURL + "...";
                messages = messages + msgString + "\n";

                //create a connection to the db - note the default account is "remote"
                //and the password is "remote_pass" - you will have to set this
                //account up in your database

                DBConn = DriverManager.getConnection(sourceURL, "remote", "remote_pass");

            } catch (Exception e) {

                errString = "\nError connecting to orderinfo database\n" + e;
                result.put("errString", errString);
                connectError = true;

            } // end try-catch

        } else {

            errString = "\nMissing customer information!!!\n";
            result.put("errString", errString);
            connectError = true;

        }// customer data check

        //If there is not a connection error, then we form the SQL statement
        //to submit the order to the orders table and then execute it.

        if (!connectError) {
            Calendar rightNow = Calendar.getInstance();

            int TheHour = rightNow.get(rightNow.HOUR_OF_DAY);
            int TheMinute = rightNow.get(rightNow.MINUTE);
            int TheSecond = rightNow.get(rightNow.SECOND);
            int TheDay = rightNow.get(rightNow.DAY_OF_WEEK);
            int TheMonth = rightNow.get(rightNow.MONTH);
            int TheYear = rightNow.get(rightNow.YEAR);
            orderTableName = "order" + String.valueOf(rightNow.getTimeInMillis());
            
            result.put("orderNumber", orderTableName);

            String dateTimeStamp = TheMonth + "/" + TheDay + "/" + TheYear + " "
                    + TheHour + ":" + TheMinute + ":" + TheSecond;

            // Get the order data
            firstName = (String) param.get("firstName");
            lastName = (String) param.get("lastName");
            phoneNumber = (String) param.get("phoneNumber");
            customerAddress = (String) param.get("customerAddress");
            sTotalCost = (String) param.get("sTotalCost");
            beginIndex = 0;
            beginIndex = sTotalCost.indexOf("$", beginIndex) + 1;
            sTotalCost = sTotalCost.substring(beginIndex, sTotalCost.length());
            fCost = Float.parseFloat(sTotalCost);

            try {
                s = DBConn.createStatement();

                SQLstatement = ("CREATE TABLE " + orderTableName
                        + "(item_id int unsigned not null auto_increment primary key, "
                        + "product_id varchar(20), description varchar(80), "
                        + "item_price float(7,2) );");

                executeUpdateVal = s.executeUpdate(SQLstatement);

            } catch (Exception e) {

                errString = "\nProblem creating order table " + orderTableName + ":: " + e;
                result.put("errString", errString);
                executeError = true;

            } // try

            if (!executeError) {
                try {
                    SQLstatement = ("INSERT INTO orders (order_date, " + "first_name, "
                            + "last_name, address, phone, total_cost, shipped, "
                            + "ordertable) VALUES ( '" + dateTimeStamp + "', "
                            + "'" + firstName + "', " + "'" + lastName + "', "
                            + "'" + customerAddress + "', " + "'" + phoneNumber + "', "
                            + fCost + ", " + false + ", '" + orderTableName + "' );");

                    executeUpdateVal = s.executeUpdate(SQLstatement);

                } catch (Exception e1) {

                    errString = "\nProblem with inserting into table orders:: " + e1;
                    result.put("errString", errString);
                    executeError = true;

                    try {
                        SQLstatement = ("DROP TABLE " + orderTableName + ";");
                        executeUpdateVal = s.executeUpdate(SQLstatement);

                    } catch (Exception e2) {

                        errString = "\nProblem deleting unused order table:: "
                                + orderTableName + ":: " + e2;
                        result.put("errString", errString);

                    } // try

                } // try

            } //execute error check

        }

        // Now, if there is no connect or SQL execution errors at this point, 
        // then we have an order added to the orderinfo::orders table, and a 
        // new ordersXXXX table created. Here we insert the list of items in
        // jTextArea2 into the ordersXXXX table.

        if (!connectError && !executeError) {
            // Now we create a table that contains the itemized list
            // of stuff that is associated with the order

            String[] items = ((String) param.get("orderString")).split("\\n");

            for (int i = 0; i < items.length; i++) {
                orderItem = items[i];
                messages = messages + ("\nitem #:" + i + ": " + items[i]);

                // Check just to make sure that a blank line was not stuck in
                // there... just in case.

                if (orderItem.length() > 0) {
                    // Parse out the product id
                    beginIndex = 0;
                    endIndex = orderItem.indexOf(" : ", beginIndex);
                    productID = orderItem.substring(beginIndex, endIndex);

                    // Parse out the description text
                    beginIndex = endIndex + 3; //skip over " : "
                    endIndex = orderItem.indexOf(" : ", beginIndex);
                    description = orderItem.substring(beginIndex, endIndex);

                    // Parse out the item cost
                    beginIndex = endIndex + 4; //skip over " : $"
                    //endIndex = orderItem.indexOf(" : ",orderItem.length());
                    //sPerUnitCost = orderItem.substring(beginIndex,endIndex);
                    sPerUnitCost = orderItem.substring(beginIndex, orderItem.length());
                    perUnitCost = Float.parseFloat(sPerUnitCost);

                    SQLstatement = ("INSERT INTO " + orderTableName
                            + " (product_id, description, item_price) "
                            + "VALUES ( '" + productID + "', " + "'"
                            + description + "', " + perUnitCost + " );");
                    try {
                        executeUpdateVal = s.executeUpdate(SQLstatement);
                        messages = "\nORDER SUBMITTED FOR: " + firstName + " " + lastName;
                        result.put("messages", messages);

                        // Clean up the display

                        /*
                         * jTextArea1.setText(""); jTextArea2.setText("");
                         * jTextArea4.setText(""); jTextField3.setText("");
                         * jTextField4.setText(""); jTextField5.setText("");
                         * jTextField6.setText("$0");
                         */

                    } catch (Exception e) {

                        errString = "\nProblem with inserting into table " + orderTableName
                                + ":: " + e;
                        result.put("errString", errString);

                    } // try

                } // line length check

            } //for each line of text in order table

        }


        return result;
    }

    // Implementation for GETPENDING request
    private Map<String, Object> getPendingOrders(Map<String, Object> m) {

        // This method is responsible for querying the orders database and
        // getting the list of pending orders. This are orders that have not
        // been shipped as of yet. The list of pending orders is written to
        // jTextArea1.

        Boolean connectError = false;       // Error flag
        Connection DBConn = null;           // MySQL connection handle
        String errString = null;            // String for displaying errors
        String msgString = null;            // String for displaying non-error messages
        ResultSet res = null;               // SQL query result set pointer
        Statement s = null;                 // SQL statement pointer
        int shippedStatus;                  // if 0, order not shipped, if 1 order shipped
        String messages = "";
        HashMap<String, Object> param = (HashMap<String, Object>) m;

        HashMap<String, Object> result = new HashMap<String, Object>();
        String rows = "";
        // Clean up the form before we start

        /*
         * jTextArea1.setText(""); jTextArea2.setText("");
         * jTextArea3.setText(""); jTextField2.setText("");
         * jTextField3.setText(""); jTextField4.setText("");
         * jTextField5.setText("");
         */

        // Connect to the order database
        try {
            msgString = ">> Establishing Driver...";
            messages = messages + ("\n" + msgString);

            //load JDBC driver class for MySQL
            Class.forName("com.mysql.jdbc.Driver");

            msgString = ">> Setting up URL...";
            messages = messages + ("\n" + msgString);

            //define the data source
            //     String SQLServerIP = (String)param.get("SQLServerIP");
            String sourceURL = "jdbc:mysql://" + SQLServerIP + ":3306/orderinfo";

            msgString = ">> Establishing connection with: " + sourceURL + "...";
            messages = messages + ("\n" + msgString);

            //create a connection to the db - note the default account is "remote"
            //and the password is "remote_pass" - you will have to set this
            //account up in your database
            DBConn = DriverManager.getConnection(sourceURL, "remote", "remote_pass");

        } catch (Exception e) {

            errString = "\nProblem connecting to orderinfo database:: " + e;
            result.put("errString", e.toString());
            connectError = true;

        } // end try-catch

        // If we are connected, then we get the list of trees from the
        // inventory database
        if (!connectError) {
            try {
                // Create a query to get all the orders and execute the query
                s = DBConn.createStatement();
                res = s.executeQuery("Select * from orders");

                //Display the data in the textarea


                // For each row returned, we check the shipped status. If it is
                // equal to 0 it means it has not been shipped as of yet, so we
                // display it in TextArea 1. Note that we use an integer because
                // MySQL stores booleans and a TinyInt(1), which we interpret
                // here on the application side as an integer. It works, it just
                // isn't very elegant.
                while (res.next()) {
                    shippedStatus = Integer.parseInt(res.getString(8));

                    if (shippedStatus == 0) {
                        msgString = "ORDER # " + res.getString(1) + " : " + res.getString(2)
                                + " : " + res.getString(3) + " : " + res.getString(4);
                        rows = rows + (msgString + "\n");

                    } // shipped status check

                } // while

                // notify the user all went well and enable the select order
                // button
                //   jButton3.setEnabled(true);
                msgString = "\nPENDING ORDERS RETRIEVED...";
                messages = messages + msgString;
                result.put("messages", messages);
                result.put("rows", rows);
            } catch (Exception e) {

                errString = "\nProblem getting tree inventory:: " + e;
                result.put("errString", e.toString());

            } // end try-catch

        } // if connect check

        return result;

    } // getPendingOrders

    // Implementation for GETSHIPPED request
    private Map<String, Object> getShippedOrders(Map<String, Object> m) {

        // This method is responsible for querying the orders database and
        // getting the list of orders that have been shipped. The list of shipped
        // orders is written to jTextArea1.

        Boolean connectError = false;       // Error flag
        Connection DBConn = null;           // MySQL connection handle
        String errString = null;            // String for displaying errors
        String msgString = null;            // String for displaying non-error messages
        ResultSet res = null;               // SQL query result set pointer
        Statement s = null;                 // SQL statement pointer
        int shippedStatus;                  // if 0, order not shipped, if 1 order shipped
        String messages = "";
        HashMap<String, Object> param = (HashMap<String, Object>) m;

        HashMap<String, Object> result = new HashMap<String, Object>();
        String rows = "";
        // Clean up the form before we start

        /*
         * jTextArea1.setText(""); jTextArea2.setText("");
         * jTextArea3.setText(""); jTextField2.setText("");
         * jTextField3.setText(""); jTextField4.setText("");
         * jTextField5.setText("");
         */

        // Connect to the order database
        try {
            msgString = ">> Establishing Driver...";
            messages = messages + ("\n" + msgString);

            //load JDBC driver class for MySQL
            Class.forName("com.mysql.jdbc.Driver");

            msgString = ">> Setting up URL...";
            messages = messages + ("\n" + msgString);

            //define the data source
            //        String SQLServerIP = (String)param.get("SQLServerIP");
            String sourceURL = "jdbc:mysql://" + SQLServerIP + ":3306/orderinfo";

            msgString = ">> Establishing connection with: " + sourceURL + "...";
            messages = messages + ("\n" + msgString);

            //create a connection to the db - note the default account is "remote"
            //and the password is "remote_pass" - you will have to set this
            //account up in your database
            DBConn = DriverManager.getConnection(sourceURL, "remote", "remote_pass");

        } catch (Exception e) {

            errString = "\nProblem connecting to orderinfo database:: " + e;
            result.put("errString", e.toString());
            connectError = true;

        } // end try-catch

        // If we are connected, then we get the list of trees from the
        // inventory database

        if (!connectError) {
            try {
                // Create a query to get all the rows from the orders database
                // and execute the query.
                s = DBConn.createStatement();
                res = s.executeQuery("Select * from orders");

                //Display the data in the textarea
                rows = "";

                // For each row returned, we check the shipped status. If it is
                // equal to 0 it means it has not been shipped as of yet, so we
                // display it in TextArea 1. Note that we use an integer because
                // MySQL stores booleans and a TinyInt(1), which we interpret
                // here on the application side as an integer. It works, it just
                // isn't very elegant.

                while (res.next()) {
                    shippedStatus = Integer.parseInt(res.getString(8));

                    if (shippedStatus == 1) {
                        msgString = "SHIPPED ORDER # " + res.getString(1) + " : " + res.getString(2)
                                + " : " + res.getString(3) + " : " + res.getString(4);
                        rows = rows + (msgString + "\n");

                    } // shipped status check

                } // while

                /*
                 * jButton1.setEnabled(false); jButton3.setEnabled(false);
                 */

                messages = messages + "\nSHIPPED ORDERS RETRIEVED...";
                result.put("messages", messages);
                result.put("rows", rows);
            } catch (Exception e) {

                errString = "\nProblem getting tree inventory:: " + e;
                result.put("errString", e.toString());

            } // end try-catch

        } // connect check
        return result;
    } //

    // Implementation for MARKSHIPPED request
    private Map<String, Object> markAsShipped(Map<String, Object> m) {
        // This method is responsible changing the status of the order
        // to shipped.

        Boolean connectError = false;       // Error flag
        Connection DBConn = null;           // MySQL connection handle
        String errString = null;            // String for displaying errors
        String msgString = null;            // String for displaying non-error messages
        ResultSet res = null;               // SQL query result set pointer
        int rows;                           // Rows updated
        Statement s = null;                 // SQL statement pointer
        String SQLStatement = null;         // SQL statement string
        String messages = "";
        String rows2 = "";

        HashMap<String, Object> result = new HashMap<String, Object>();
        HashMap<String, Object> param = (HashMap<String, Object>) m;

        // Connect to the order database
        try {
            msgString = ">> Establishing Driver...";
            messages = messages + ("\n" + msgString);

            //load JDBC driver class for MySQL
            Class.forName("com.mysql.jdbc.Driver");

            msgString = ">> Setting up URL...";
            messages = messages + ("\n" + msgString);

            //define the data source
            //  String SQLServerIP = (String)param.get("SQLServerIP");
            String sourceURL = "jdbc:mysql://" + SQLServerIP + ":3306/orderinfo";

            msgString = ">> Establishing connection with: " + sourceURL + "...";
            messages = messages + ("\n" + msgString);

            //create a connection to the db - note the default account is "remote"
            //and the password is "remote_pass" - you will have to set this
            //account up in your database
            DBConn = DriverManager.getConnection(sourceURL, "remote", "remote_pass");

        } catch (Exception e) {

            errString = "\nProblem connecting to orderinfo database:: " + e;
            result.put("errString", e.toString());
            connectError = true;

        } // end try-catch

        // If we are connected, then we update the shipped status

        if (!connectError) {
            try {
                // first we create the query
                s = DBConn.createStatement();
                SQLStatement = "UPDATE orders SET shipped=" + true + " WHERE order_id=" + ((Integer)param.get("updateOrderID")).toString();

                // execute the statement
                rows = s.executeUpdate(SQLStatement);

                // if the query worked, then we display the data in TextArea 4 - BTW, its highly
                // unlikely that the row won't exist and if it does the database tables are
                // really screwed up... this should not fail if you get here, but the check (in the
                // form of the else clause) is in place anyway

                if (rows > 0) {
                    rows2 = rows2 + ("\nOrder #" + ((Integer)param.get("updateOrderID")).toString() + " status has been changed to shipped.");

                } else {

                    rows2 = rows2 + ("\nOrder #" + ((Integer)param.get("updateOrderID")).toString() + " record not found.");

                } // execute check

                // Clean up the form
             /*
                 * jButton1.setEnabled(false); jButton3.setEnabled(false);
                 * jTextArea1.setText(""); jTextArea2.setText("");
                 * jTextArea3.setText(""); jTextField2.setText("");
                 * jTextField3.setText(""); jTextField4.setText("");
                 * jTextField5.setText("");
                 */

                result.put("messages", messages);
                result.put("rows", rows2);
                result.put("orderNumber", ((Integer)param.get("updateOrderID")).toString());

            } catch (Exception e) {

                errString = "\nProblem updating status:: " + e;
                result.put("errString", e.toString());
                // jTextArea1.setText("");

            } // end try-catch



        } // if connect check

        return result;
    }
    
    
    private Map<String,Object> addInventory(Map<String,Object> m){    
        // TODO add your handling code here:
        Boolean connectError = false;   // Error flag
        Connection DBConn = null;       // MySQL connection handle
        String description;             // Tree, seed, or shrub description
        Boolean executeError = false;   // Error flag
        String errString = null;        // String for displaying errors
        int executeUpdateVal;           // Return value from execute indicating effected rows
        String msgString = null;        // String for displaying non-error messages
        ResultSet res = null;           // SQL query result set pointer
        String tableSelected = null;    // String used to determine which data table to use
        Integer quantity;               // Quantity of trees, seeds, or shrubs
        Float perUnitCost;              // Cost per tree, seed, or shrub unit
        String productID = null;        // Product id of tree, seed, or shrub
        java.sql.Statement s = null;    // SQL statement pointer
        String SQLstatement = null;     // String for building SQL queries
        
        HashMap<String,Object> result = new HashMap<String,Object>();
        HashMap<String,Object> param = (HashMap<String,Object>)m;
        
        String messages = "";
        String rows = "";
        
        // Check to make sure a radio button is selected
 /*       if (jRadioButton1.isSelected() | jRadioButton2.isSelected() | jRadioButton3.isSelected())
        {
            fieldError = false;
        } else {

            msgString = "Must select Tree, Seeds, or Shrubs radio button.";
            jTextArea1.setText("\n"+msgString);
        }

        //Make sure there is a product description
        if ( jTextField5.getText().length() > 0 )
        {
            fieldError = false;
        } else {
            
            msgString = "Must enter a product description.";
            jTextArea1.setText("\n"+msgString);
        }
        
        //Make sure there is a product ID
        if ( jTextField2.getText().length() > 0 )
        {
            fieldError = false;
        } else {
            
            msgString = "Must enter a product ID.";
            jTextArea1.setText("\n"+msgString);
        }

        //Make sure there is a price
        if ( jTextField3.getText().length() > 0 )
        {
            fieldError = false;
        } else {

            msgString = "Must enter a product price.";
            jTextArea1.setText("\n"+msgString);
        }

        //Make sure there is a quantity
        if ( jTextField4.getText().length() > 0 )
        {
            fieldError = false;
        } else {

            msgString = "Must enter a product quantity.";
            jTextArea1.setText("\n"+msgString);
        } */

        //Now, if there was no error in the data fields, we try to
        //connect to the database.
 //       if ( !fieldError )
 //       {
            try
            {
                msgString = ">> Establishing Driver...";
                messages = messages + ("\n"+msgString);

                //load JDBC driver class for MySQL
                Class.forName( "com.mysql.jdbc.Driver" );

                msgString = ">> Setting up URL...";
                messages = messages + ("\n"+msgString);

                //define the data source
               String sourceURL = "jdbc:mysql://" + SQLServerIP + ":3306/inventory";

                msgString = ">> Establishing connection with: " + sourceURL + "...";
                messages = messages + ("\n"+msgString);

                //create a connection to the db
                DBConn = DriverManager.getConnection(sourceURL,"remote","remote_pass");

            } catch (Exception e) {

                errString =  "\nProblem connecting to database:: " + e;
                result.put("errString",errString);
                connectError = true;

            } // end try-catch
   //     } // fieldError check

        //If there is not connection error, then we form the SQL statement
        //and then execute it.

        if (!connectError )
        {
            try
            {
                // get the data from the text fields
                description = (String)param.get("description");
                productID = (String)param.get("productID");
                quantity = Integer.parseInt((String)param.get("quantity"));
                perUnitCost = Float.parseFloat((String)param.get("perUnitCost"));

                // create an SQL statement variable and create the INSERT
                // query to insert the new inventory into the database

                s = DBConn.createStatement();

                // if trees are selected then insert inventory into trees
                // table
                if ( ((String)param.get("category")).equals("trees"))
                {
                    SQLstatement = ( "INSERT INTO trees (product_code, " +
                            "description, quantity, price) VALUES ( '" +
                            productID + "', " + "'" + description + "', " +
                            quantity + ", " + perUnitCost + ");");

                }

                // if shrubs are selected then insert inventory into strubs
                // table
                if (((String)param.get("category")).equals("shrubs"))
                {
                    SQLstatement = ( "INSERT INTO shrubs (product_code, " +
                            "description, quantity, price) VALUES ( '" +
                            productID + "', " + "'" + description + "', " +
                            quantity + ", " + perUnitCost + ");");
                }

                // if seeds are selected then insert inventory into seeds
                // table
                if (((String)param.get("category")).equals("seeds"))
                {
                    SQLstatement = ( "INSERT INTO seeds (product_code, " +
                            "description, quantity, price) VALUES ( '" +
                            productID + "', " + "'" + description + "', " +
                            quantity + ", " + perUnitCost + ");");
                }

                // execute the update
                executeUpdateVal = s.executeUpdate(SQLstatement);

                // let the user know all went well
                errString =  "\nINVENTORY UPDATED...";
                result.put("errString", errString);

            } catch (Exception e) {

                errString =  "\nProblem with insert:: " + e;
                result.put("errString", errString);
                executeError = true;

            } // try

        } //execute SQL check

        //If the execute when OK, then we lList the contents of the table

        if ( !connectError && !executeError )
        {
            try
            {
                // create another SQL statement
                s = DBConn.createStatement();

                // now we build a query to list the inventory table contents
                // for the user
                // ... here is the SQL for trees
                if (((String)param.get("category")).equals("trees"))
                {
                    res = s.executeQuery( "Select * from trees" );
                    tableSelected = "TREE";
                }
                // ... here is the SQL for shrubs
                if (((String)param.get("category")).equals("shrubs"))
                {
                    res = s.executeQuery( "Select * from shrubs" );
                    tableSelected = "SHRUB";
                }
                // ... here is the SQL for seeds
                if (((String)param.get("category")).equals("seeds"))
                {
                    res = s.executeQuery( "Select * from seeds" );
                    tableSelected = "SEED";
                }

                // Now we list the inventory for the selected table
                
                while (res.next())
                {
                    msgString = tableSelected + ">> " + res.getString(1) + " :: " + res.getString(2) +
                            " :: "+ res.getString(3) + " :: " + res.getString(4);
                    rows = rows+("\n"+msgString);

                } // while
                
                result.put("rows",rows);
                
                

            } catch(Exception e) {

                errString =  "\nProblem with " + tableSelected +" query:: " + e;
                result.put("errString",errString);
                executeError = true;

            } // try
        }
        return result;
    }
    
    
    private Map<String,Object> listInventory(Map<String,Object> m){
         // This button will list the inventory for the product selected by the
        // radio button

        Boolean connectError = false;   // Error flag
        Connection DBConn = null;       // MySQL connection handle
        Boolean executeError = false;   // Error flag
        String errString = null;        // String for displaying errors
        String msgString = null;        // String for displaying non-error messages
        ResultSet res = null;           // SQL query result set pointer
        String tableSelected = null;    // String used to determine which data table to use
        java.sql.Statement s = null;    // SQL statement pointer
        
        String messages = "";
        String rows = "";
        HashMap<String,Object> result = new HashMap<String,Object>();
        HashMap<String,Object> param = (HashMap<String,Object>) m;

        // Check to make sure a radio button is selected
   /*     if (jRadioButton1.isSelected() | jRadioButton2.isSelected() | jRadioButton3.isSelected())
        {
            fieldError = false;

        } else {

            msgString = "Must select Tree, Seeds, or Shrubs radio button.";
            jTextArea1.setText("\n"+msgString);
        } */


        //Now, we try to connect to the inventory database.
   //     if (!fieldError)
   //      {
            //Clear the fields - they are not needed and may cause confusion
            
            try
            {
                msgString = ">> Establishing Driver...";
                messages = messages + ("\n"+msgString);

                //load JDBC driver class for MySQL
                Class.forName( "com.mysql.jdbc.Driver" );

                msgString = ">> Setting up URL...";
               messages = messages + ("\n"+msgString);

                //define the data source
          //      String SQLServerIP = jTextField1.getText();
                String sourceURL = "jdbc:mysql://" + SQLServerIP + ":3306/inventory";

                msgString = ">> Establishing connection with: " + sourceURL + "...";
                messages = messages + ("\n"+msgString);

                //create a connection to the db
                DBConn = DriverManager.getConnection(sourceURL,"remote","remote_pass");

            } catch (Exception e) {

                errString =  "\nProblem connecting to database:: " + e;
                result.put("errString",errString);
                connectError = true;

            } // end try-catch

   //     } // fielderror check - make sure a product is selected
    
        //If there is not connection error, then we form the SQL statement
        //and then execute it.

        if (!connectError )
        {
            try
            {
                // create an SQL statement variable and create the INSERT
                // query to insert the new inventory into the database

                s = DBConn.createStatement();

                // now we build a query to list the inventory table contents
                // for the user
                // ... here is the SQL for trees
                if ( ((String)param.get("category")).equals("trees"))
                {
                    res = s.executeQuery( "Select * from trees" );
                    tableSelected = "TREE";
                }
                // ... here is the SQL for shrubs
                if (((String)param.get("category")).equals("shrubs"))
                {
                    res = s.executeQuery( "Select * from shrubs" );
                    tableSelected = "SHRUB";
                }
                // ... here is the SQL for seeds
                if (((String)param.get("category")).equals("seeds"))
                {
                    res = s.executeQuery( "Select * from seeds" );
                    tableSelected = "SEED";
                }

                // Now we list the inventory for the selected table
             //   jTextArea1.setText("");
                while (res.next())
                {
                    msgString = tableSelected+">> " + res.getString(1) + " :: " + res.getString(2) +
                            " :: "+ res.getString(3) + " :: " + res.getString(4);
                    rows = rows + ("\n"+msgString);

                } // while
                
                result.put("rows", rows);
            } catch(Exception e) {

                errString =  "\nProblem with " + tableSelected +" query:: " + e;
                result.put("errString",errString);
                executeError = true;

            } // try
        }
        
        return result;
    }
               
}